﻿using Microsoft.AspNetCore.Http;
using Microsoft.VisualBasic;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.Storage;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Ocsp;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace slwhTools.ExcelHelper
{
    public class SExcelHelper<T> : ISExcelFactory<T>
    {
        public ISExcel sexl = new SExcel();
        public SExcelHelper()
        {
            sexl.SExcelInit();
        }

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <typeparam name=">"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public void ImportExcel<T>(IEnumerable<T> list) where T : new()
        {
            //创建工作表
            if (ISExcel.sheet == null)
            {
                ISExcel.sheet = ISExcel.workbook.CreateSheet("Sheet1");
            }

            // 获取属性与列名的映射
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            //创建标题
            IRow headerRow = ISExcel.sheet.CreateRow(ISExcel.TitleRowIndex);
            var cell = ISExcel.TitleCellIndex;
            foreach (var item in properties)
            {
                var CellName = "";
                CellName = item.GetCustomAttribute<SColumnAttribute>()?.Name;

                if (string.IsNullOrEmpty(CellName))
                {
                    //获取字段名称
                    CellName = item.Name;
                }

                headerRow.CreateCell(cell).SetCellValue(CellName);
                cell++;
            }

            //赋值内容
            int rowIndex = ISExcel.StartRowIndex;
            foreach (var item in list)
            {
                var row = ISExcel.sheet.CreateRow(rowIndex++);

                for (int i = 0; i < properties.Length; i++)
                {
                    var value = properties[i].GetValue(item);
                    SetCellValue(row, i + ISExcel.StartCellIndex, value);
                }

                if (ISExcel.type == WorkType.SXLSX)
                {
                    // 每1000行刷新一次（优化内存）
                    if (rowIndex % 1000 == 0)
                    {
                        ((SXSSFSheet)ISExcel.sheet).FlushRows();
                    }
                }
            }
        }

        /// <summary>
        /// 返回文件流
        /// </summary>
        /// <returns></returns>
        public MemoryStream RStream() {
            MemoryStream stream = new MemoryStream();
            ISExcel.workbook.Write(stream, false);

            return stream;
        }

        /// <summary>
        /// 读取控制器上传的excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public IEnumerable<T> ReadExcelFile<T>(IFormFile file) where T : new()
        {
            // 检查文件扩展名
            var extension = Path.GetExtension(file.FileName).ToLower();
            if (extension != ".xlsx" && extension != ".xls")
            {
                throw new Exception("请传入正确的文件格式");
            }

            // 读取Excel文件
            using (var stream = new MemoryStream())
            {
                file.CopyToAsync(stream);

                var list = ReadExcel<T>(stream);

                return list;
            }
        }

        /// <summary>
        /// 从 Excel 流中读取数据并转换为指定类型的集合
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="stream">Excel 文件流</param>
        /// <returns>转换后的对象集合</returns>
        public IEnumerable<T> ReadExcel<T>(MemoryStream? stream) where T : new()
        {
            var result = new List<T>();
            stream.Position = 0;//重置流位置

            // 根据文件类型创建工作簿
            IWorkbook workbook;
            try
            {
                // 尝试读取为 XLSX 格式
                workbook = new XSSFWorkbook(stream);
            }
            catch (Exception ex)
            {
                // 如果不是 XLSX 格式，尝试读取为 XLS 格式
                stream.Position = 0; // 重置流位置
                workbook = new HSSFWorkbook(stream);
            }

            if (workbook.NumberOfSheets == 0)
            {
                throw new InvalidOperationException("Excel 文件中没有工作表");
            }

            // 默认读取第一个工作表
            ISheet sheet = workbook.GetSheetAt(0);

            // 获取属性与列名的映射
            var properties = typeof(T).GetProperties();
            var columnMappings = GetColumnMappings(sheet, properties);

            // 从第二行开始读取数据（假设第一行是标题）
            for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                var item = new T();
                bool hasValue = false;

                foreach (var mapping in columnMappings)
                {
                    ICell cell = row.GetCell(mapping.ColumnIndex);
                    if (cell != null)
                    {
                        object cellValue = GetCellValue(cell);
                        if (cellValue != null)
                        {
                            hasValue = true;
                            SetPropertyValue(item, mapping.Property, cellValue);
                        }
                    }
                }

                if (hasValue)
                {
                    result.Add(item);
                }
            }

            return result;
        }

        

        /// <summary>
        /// 获取单元格值
        /// </summary>
        private object GetCellValue(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    return cell.NumericCellValue;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Formula:
                    return GetCellValue(cell);
                case CellType.Blank:
                    return null;
                default:
                    return cell.ToString();
            }
        }

        /// <summary>
        /// 赋值内容
        /// </summary>
        /// <param name="row"></param>
        /// <param name="cellIndex"></param>
        /// <param name="value"></param>
        private static void SetCellValue(IRow row, int cellIndex, object value)
        {
            if (value == null)
            {
                row.CreateCell(cellIndex).SetCellValue(string.Empty);
                return;
            }

            var cell = row.CreateCell(cellIndex);

            switch (value)
            {
                case string str:
                    cell.SetCellValue(str);
                    break;
                case int num:
                    cell.SetCellValue(num);
                    break;
                case double dbl:
                    cell.SetCellValue(dbl);
                    break;
                case decimal dec:
                    cell.SetCellValue((double)dec);
                    break;
                case DateTime date:
                    cell.SetCellValue(date);
                    // 可以添加日期格式样式
                    break;
                case bool b:
                    cell.SetCellValue(b);
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        /// <summary>
        /// 获取属性与 Excel 列的映射关系
        /// </summary>
        private List<(PropertyInfo Property, int ColumnIndex)> GetColumnMappings(ISheet sheet, PropertyInfo[] properties)
        {
            var mappings = new List<(PropertyInfo, int)>();

            // 获取标题行
            IRow headerRow = sheet.GetRow(0);
            if (headerRow == null) return mappings;

            // 遍历标题行中的单元格
            for (int colIndex = 0; colIndex < headerRow.LastCellNum; colIndex++)
            {
                ICell cell = headerRow.GetCell(colIndex);
                if (cell == null) continue;

                string header = cell.ToString()?.Trim();
                if (string.IsNullOrEmpty(header)) continue;

                // 查找匹配的属性（不区分大小写）
                var property = properties.FirstOrDefault(p =>
                    p.Name.Equals(header, StringComparison.OrdinalIgnoreCase) ||
                    (p.GetCustomAttribute<SColumnAttribute>()?.Name?.Equals(header, StringComparison.OrdinalIgnoreCase) ?? false));

                if (property != null)
                {
                    mappings.Add((property, colIndex));
                }
            }

            return mappings;
        }

        /// <summary>
        /// 设置属性值，处理类型转换
        /// </summary>
        private void SetPropertyValue<T>(T item, PropertyInfo property, object value)
        {
            try
            {
                if (value == null) return;

                var targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;

                if (targetType == typeof(DateTime))
                {
                    // 处理日期类型
                    if (value is DateTime dateValue)
                    {
                        property.SetValue(item, dateValue);
                    }
                    else if (DateTime.TryParse(value.ToString(), out dateValue))
                    {
                        property.SetValue(item, dateValue);
                    }
                }
                else if (targetType.IsEnum)
                {
                    // 处理枚举类型
                    if (Enum.TryParse(targetType, value.ToString(), true, out var enumValue))
                    {
                        property.SetValue(item, enumValue);
                    }
                }
                else if (targetType == typeof(bool))
                {
                    // 处理布尔类型
                    if (bool.TryParse(value.ToString(), out var boolValue))
                    {
                        property.SetValue(item, boolValue);
                    }
                    else
                    {
                        // 处理 "是/否"、"Y/N" 等常见布尔表示
                        string strValue = value.ToString().ToLower();
                        property.SetValue(item, strValue == "是" || strValue == "y" || strValue == "true" || strValue == "1");
                    }
                }
                else if (targetType == typeof(decimal))
                {
                    // 处理 decimal 类型
                    if (decimal.TryParse(value.ToString(), out var decimalValue))
                    {
                        property.SetValue(item, decimalValue);
                    }
                }
                else if (targetType == typeof(double))
                {
                    // 处理 double 类型
                    if (double.TryParse(value.ToString(), out var doubleValue))
                    {
                        property.SetValue(item, doubleValue);
                    }
                }
                else
                {
                    // 基本类型转换
                    var convertedValue = Convert.ChangeType(value, targetType);
                    property.SetValue(item, convertedValue);
                }
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"设置属性 {property.Name} 值时出错: {ex.Message}", ex);
            }
        }


    }
}
